package xdsysadmin2

import com.github.drinkjava2.jdialects.Dialect
import grails.transaction.Transactional
import groovy.sql.Sql
import sysAuth.SysDepartments
import sysAuth.SysFunOpt
import sysAuth.SysFunctions
import sysAuth.SysNotice
import sysAuth.SysOperations
import sysAuth.SysOrganization
import sysAuth.SysParams
import sysAuth.SysRoleRela
import sysAuth.SysRoleRelaOperat
import sysAuth.SysRoles
import sysAuth.SysUserRoles
import sysAuth.SysUsers
import sysAuth.SysViewCfg
import sysUtil.BeetlUtil
import sysUtil.SysCommUtil
import xdTool.XdDataSource
import xdTool.XdTableCfg
import xdTool.XdTableColCfg
import xdTool.XdWidget
import xdTool.XdWidgetDate
import xdTool.XdWidgetInput
import xdTool.XdWidgetPanel
import xdTool.XdWidgetPanelParam
import xdTool.XdWidgetSelect

import javax.servlet.http.HttpSession
import javax.sql.DataSource

@Transactional
class SysDBUtilService {


    /**
     * 根据dataSource获取数据源
     * @param dataSource
     * @return
     */
    def dataSource
    def getGroovySqlInstance(){
        return new groovy.sql.Sql(dataSource: dataSource)
    }

    /**
     * 根据配置获取数据链接
     * @param xdDataSource
     * @return
     */
    def getGroovySqlInstance(XdDataSource xdDataSource){
        groovy.sql.Sql db
        if(xdDataSource && xdDataSource.state != '0'){
            db =  Sql.newInstance(xdDataSource.url,xdDataSource.username,xdDataSource.password,xdDataSource.driverClassName)
        }
        return db
    }

    /**
     * 获取数据库链接
     * @param dataSourceId
     * @return
     */
    def getGroovySqlInstanceById(dataSourceId){
        groovy.sql.Sql db
        if(dataSourceId){
            XdDataSource xdDataSource = XdDataSource.get(Long.parseLong(String.valueOf(dataSourceId)))
            db =  getGroovySqlInstance(xdDataSource)
        }else{
            db = getGroovySqlInstance()
        }
        return  db
    }

    /**
     * 获取数据库connect
     * @param dataSourceId
     * @return
     */
    def getDbConnectionById(dataSourceId){
        def db = getGroovySqlInstanceById(dataSourceId)
        return  db.getConnection()?:db.getDataSource().getConnection()
    }

    /**
     * 根据书记员ID，获取DataSource
     * @param xdDataSourceId
     * @return
     */
    def getDBDataSourceById(dataSourceId){
        def db = getGroovySqlInstanceById(dataSourceId)
        return  db.getDataSource()?:dataSource
    }

    /**
     * 根据SQL获取分页数据
     * @param sql
     * @param offset
     * @param max
     * @param db
     * @return
     */
    def pageResult(sql ,int offset,int max,groovy.sql.Sql db){
        def ds = db.getDataSource()?:db.getConnection()
        Dialect dialect = Dialect.guessDialect(ds)
        sql = dialect.pagin(offset<=0?1:offset,max,String.valueOf(sql))
        dialect = null
        //println sql
        return db.rows(sql)
    }
    /**
     * 根据sql获取总数
     * @param sql
     * @param db
     * @return
     */
    def pageCount(String sql,groovy.sql.Sql db){
        def cSql = """
            select count(1) COUNTN FROM ( ${sql} ) A
        """
        return db.firstRow(String.valueOf(cSql)).COUNTN?:0
    }

    /**
     * 获取所有数据,不分页
     * @param sql
     * @param db
     * @return
     */
    def pageAllResult(String sql,groovy.sql.Sql db){
        return db.rows(String.valueOf(sql))
    }

    /**
     * 获取DB中所有的table
     * @param xdDataSource
     * @return
     */
    def getDBTables(XdDataSource xdDataSource){
        def tableArray = []
        def  db = getGroovySqlInstance(xdDataSource)
        if(!db){
            db = getGroovySqlInstance()
        }
        def rst = db.getConnection().getMetaData().getTables(null, null,null, null)
        while(rst.next()){
            def map = new HashMap()
            map.put("TABLE_CAT",rst.getString("TABLE_CAT")?.toLowerCase())
            map.put("TABLE_SCHEM",rst.getString("TABLE_SCHEM")?.toLowerCase())
            map.put("TABLE_NAME",rst.getString("TABLE_NAME")?.toUpperCase())
            map.put("TABLE_TYPE",rst.getString("TABLE_TYPE"))
            map.put("REMARKS",rst.getString("REMARKS"))
            map.put("TYPE_CAT",rst.getString("TYPE_CAT"))
            map.put("TYPE_SCHEM",rst.getString("TYPE_SCHEM"))
            map.put("TYPE_NAME",rst.getString("TYPE_NAME"))
            tableArray.add(map)
        }
        return tableArray
    }

    /**
     * 获取表所有字段
     * @param xdDataSource
     * @param tableName
     */
    def getDBTableColumn(XdDataSource xdDataSource,String tableName){
        def columnArray = []
        def  db = getGroovySqlInstance(xdDataSource)
        if(!db){
            db = getGroovySqlInstance()
        }
        def rst = db.getConnection().getMetaData().getColumns(null, null,tableName.toUpperCase(), null)
        while (rst.next()) {
            def map = new HashMap()
            map.put("TABLE_CAT",rst.getString("TABLE_CAT")?.toLowerCase())
            map.put("TABLE_SCHEM",rst.getString("TABLE_SCHEM")?.toLowerCase())
            map.put("TABLE_NAME",rst.getString("TABLE_NAME")?.toLowerCase())
            map.put("COLUMN_NAME",rst.getString("COLUMN_NAME")?.toUpperCase())
            map.put("DATA_TYPE",rst.getString("DATA_TYPE"))
            map.put("TYPE_NAME",rst.getString("TYPE_NAME"))
            map.put("COLUMN_SIZE",rst.getString("COLUMN_SIZE"))
            map.put("REMARKS",rst.getString("REMARKS"))
            columnArray.add(map)
        }
        return columnArray
    }

    /**
     * 根据类型获取SQL
     * @param dataType
     * @param params
     * @return
     */
    def  getPanelDataSqlByType(dataType,params){
        def sql = ""
        if(dataType =="SQL"){
            if(params.dataSql){
                sql =  BeetlUtil.getStrTemplate(params,String.valueOf(params.dataSql))
            }
        }else if(dataType =="Panel"){
            if(params.dataPanelId){
                XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(Long.parseLong(String.valueOf(params.dataPanelId)))
                sql =  BeetlUtil.getStrTemplate(params,SysCommUtil.Clob2String(xdWidgetPanel.dataSql))
            }
        }
        return String.valueOf("${sql}")
    }

    /**
     * 根据类型获取SQL
     * @param dataType
     * @param params
     * @return
     */
    def  getSqlField(dataSourceId,sql){
        def data = []
        if(sql){
            def db = this.getGroovySqlInstanceById(dataSourceId)
            def metaClosure = { resultSetMeta ->
                //println  SysCommUtil.copyMap(resultSetMeta)
                for(int i=0;i <resultSetMeta.getColumnCount();i++){

                    data.add([tableName:resultSetMeta.getTableName(i+1),columnName:resultSetMeta.getColumnName(i+1)])
                }
            }
            def rowClosure = {}
            def newSql
            int whereIndex = String.valueOf(sql).toLowerCase().indexOf("where")
            if(whereIndex != -1){
                newSql = String.valueOf(sql).replaceAll(/\s[w,W][h,H][e,E][r,R][e,E]\s/," where 1=2 and ")
            }else{
                newSql = String.valueOf("select * from (${sql})  nwe_sql where 1=2")
            }
            db.eachRow(newSql,metaClosure,rowClosure)
        }
        return  data
    }



    /**
     * 获取sql中涉及的table名称，用于填报使用
     * @param dataSourceId
     * @param sql
     * @return
     */
    def  getSqlTableName(dataSourceId,sql){
        def tableStr

        def db = this.getGroovySqlInstanceById(dataSourceId)
        def metaClosure = { resultSetMeta ->

            for(int i=0;i <resultSetMeta.getColumnCount();i++){
                tableStr  = resultSetMeta.getTableName(i+1)
                break;
            }
        }
        def rowClosure = {}
        def newSql
        int whereIndex = String.valueOf(sql).toLowerCase().indexOf("where")
        if(whereIndex != -1){
            newSql = String.valueOf(sql).replaceAll(/\s[w,W][h,H][e,E][r,R][e,E]\s/," where 1=2 and ")
        }else{
            newSql = String.valueOf("select * from (${sql}) new_sql where 1=2")
        }
        db.eachRow(newSql,metaClosure,rowClosure)

        if(tableStr){

        }else{
            tableStr = "(${sql})"
        }

        return  tableStr
    }

    /**
     * 根据panel-id 生成delete-sql
     * @param panelId
     */
    def createPanelDeleteSQL(params){

        def panelId = params.panelId?:params.widgetPanelId
        XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(Long.parseLong(String.valueOf(panelId)))
        params.dataSql = SysCommUtil.Clob2String(xdWidgetPanel.dataSql)
        params.dataPanelId = xdWidgetPanel.dataPanelId
        def sql =  getPanelDataSqlByType(xdWidgetPanel.dataType,params)
        def tableName =  getSqlTableName(xdWidgetPanel.dataSourceId,sql)
        XdTableCfg xdTableCfg = XdTableCfg.get(xdWidgetPanel.dataId)
        def deleteSql = xdTableCfg.createDeleteSql(tableName)
        return  deleteSql
    }

    /**
     * 根据panel-id 生成insert-sql
     * @param panelId
     */
    def createPanelInsertSQL(params){
        def panelId = params.panelId?:params.widgetPanelId
        XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(Long.parseLong(String.valueOf(panelId)))
        params.dataSql = SysCommUtil.Clob2String(xdWidgetPanel.dataSql)
        params.dataPanelId = xdWidgetPanel.dataPanelId
        def sql =  getPanelDataSqlByType(xdWidgetPanel.dataType, params)
        def tableName =  getSqlTableName(xdWidgetPanel.dataSourceId,sql)
        XdTableCfg xdTableCfg = XdTableCfg.get(xdWidgetPanel.dataId)
        def insertSql = xdTableCfg.createInsertSql(tableName)
        return  insertSql
    }

    /**
     * 根据panel-id 生成insert-sql
     * @param panelId
     */
    def createPanelUpdateSQL(params){
        def panelId = params.panelId?:params.widgetPanelId
        XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(Long.parseLong(String.valueOf(panelId)))
        params.dataSql = SysCommUtil.Clob2String(xdWidgetPanel.dataSql)
        params.dataPanelId = xdWidgetPanel.dataPanelId
        def sql =  getPanelDataSqlByType(xdWidgetPanel.dataType, params)
        def tableName =  getSqlTableName(xdWidgetPanel.dataSourceId,sql)

        XdTableCfg xdTableCfg = XdTableCfg.get(xdWidgetPanel.dataId)
        def insertSql = xdTableCfg.createUpdateSql(tableName)
        return  insertSql
    }

    /**
     * panel 预览-参数获取
     * @param panelId
     */
    def  getPanelParamListById(panelId){
        def result = []
        List<XdWidgetPanelParam> xdWidgetPanelParam = XdWidgetPanelParam.findAll("from XdWidgetPanelParam as a where a.panelId = ${panelId} order by a.orderNum")
        xdWidgetPanelParam?.each {
            def classFlag = it.classFlag
            switch (it.classFlag){
                case "xd-input":
                    def map = SysCommUtil.copyMap(XdWidgetInput.get(it.paramId))
                    map.put("classFlag",classFlag)
                    result.add(map)
                    break;
                case "xd-select":
                    def map = SysCommUtil.copyMap(XdWidgetSelect.get(it.paramId))
                    map.put("classFlag",classFlag)
                    result.add(map)
                    break;
                case "xd-date":
                    def map = SysCommUtil.copyMap(XdWidgetDate.get(it.paramId))
                    map.put("classFlag",classFlag)
                    result.add(map)
                    break;
            }
        }
        return result
    }

}
